﻿using System;
using System.Collections.Generic;
using Microsoft.Data.Sqlite;

namespace DataAccessLibrary
{
    public static class DataAccess
    {
        private const string ConnectionString = "Filename = data.db";

        /// <summary>
        /// 初始化数据库
        /// </summary>
        public static void InitializeDatabase()
        {
            using (var db = new SqliteConnection(ConnectionString)) {
                db.Open();

                String tableCommand = @"CREATE TABLE IF NOT EXISTS [ConsumingRecords](
                                          [cdate] DATE NOT NULL, 
                                          [ctype] NVARCHAR(20) NOT NULL, 
                                          [cvalue] FLOAT NOT NULL, 
                                          PRIMARY KEY([cdate], [ctype], [cvalue]));";

                var createTable = new SqliteCommand(tableCommand, db);

                createTable.ExecuteReader();
            }
        }

        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="record">记录实例</param>
        public static void AddData(ConsumeRecord record)
        {
            using (var db = new SqliteConnection(ConnectionString)) {
                db.Open();

                var insertCommand = new SqliteCommand();
                insertCommand.Connection = db;

                // Use parameterized query to prevent SQL injection attacks
                insertCommand.CommandText = "INSERT INTO ConsumingRecords VALUES (@cdate, @ctype, @cvalue);";
                insertCommand.Parameters.AddWithValue("@cdate", record.Date);
                insertCommand.Parameters.AddWithValue("@ctype", record.Type);
                insertCommand.Parameters.AddWithValue("@cvalue", record.Value);

                insertCommand.ExecuteReader();

                db.Close();
            }
        }

        /// <summary>
        /// 获取记录
        /// </summary>
        /// <param name="sql">执行的SQL语句</param>
        /// <param name="parameters">参数列表</param>
        /// <returns>记录列表</returns>
        public static List<ConsumeRecord> GetData(string sql, IEnumerable<SqliteParameter> parameters = null)
        {
            var records = new List<ConsumeRecord>();

            using (var db = new SqliteConnection(ConnectionString)) {
                db.Open();

                var selectCommand = new SqliteCommand(sql, db);
                if (parameters != null)
                    selectCommand.Parameters.AddRange(parameters);

                SqliteDataReader query = selectCommand.ExecuteReader();

                while (query.Read()) {
                    var record = new ConsumeRecord
                    {
                        Date = query.GetDateTime(0),
                        Type = query.GetString(1),
                        Value = query.GetFloat(2)
                    };
                    records.Add(record);
                }

                db.Close();
            }

            return records;
        }
    }
}
